package com;

import java.util.ArrayList;

public class ViolationsManager extends Database{

	// Calling addViolations(-1) will add violations from all rules
	public void addViolations(int ruleID){
		try{
			connectToDatabase();
			
			RulesManager rulesManager = new RulesManager();
			
			ArrayList<Rule> rules = rulesManager.queryRules(ruleID);
			for (Rule r : rules){
				ArrayList<Violation> violations = r.getViolations();
				
				for (Violation v : violations){
					preparedStatement = connect.prepareStatement("REPLACE INTO `citiquantsdb`.`violations`" + 
							"(`Rule Text`," + 
							"`City`," +
							"`Year`," +
							"`Data Field`)" + 
							"VALUES(?, ?, ?, ?);");
						
						preparedStatement.setString(1, v.getRuleText());
						preparedStatement.setString(2, v.getCity());
						preparedStatement.setString(3, v.getYear());
						preparedStatement.setString(4, v.getDataField());
						
						preparedStatement.executeUpdate();
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}	
	}
	
	// Calling deleteViolations(-1) will delete all violations
	public void deleteViolations(int ruleID){
		try{
			connectToDatabase();
			
			if (ruleID != -1){
				RulesManager rulesManager = new RulesManager();

				ArrayList<Violation> violations = rulesManager.queryRules(ruleID).get(0).getViolations();
				
				for (Violation v : violations){
					preparedStatement = connect.prepareStatement("DELETE FROM `citiquantsdb`.`violations` WHERE `Rule Text` = ?;");
					preparedStatement.setString(1, v.getRuleText());
					preparedStatement.executeUpdate();
				}
			}else{
				preparedStatement = connect.prepareStatement("DELETE FROM `citiquantsdb`.`violations`");
				preparedStatement.executeUpdate();
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		
	}
	
	public ArrayList<Violation> queryViolations(String ruleName){
		ArrayList<Violation> ret = new ArrayList<Violation>();

		try{
			connectToDatabase();
			preparedStatement = connect.prepareStatement("SELECT * FROM `citiquantsdb`.`violations` WHERE `Rule Text` LIKE ?;");
			preparedStatement.setString(1, ((ruleName == null || ruleName.equals(""))? "%" : ruleName) + ":%");
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				Violation v = new Violation(resultSet.getString("Rule Text"),
						resultSet.getString("City"),
						resultSet.getString("Year"),
						resultSet.getString("Data Field"));
				ret.add(v);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return ret;
	}
	
}
